package com.tank.crate;


import com.alibaba.fastjson.JSON;
import io.crate.action.sql.SQLResponse;
import io.crate.client.CrateClient;
import io.crate.shade.org.elasticsearch.action.ActionFuture;
import io.crate.shade.org.joda.time.DateTime;
import lombok.extern.slf4j.Slf4j;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Random;
import java.util.UUID;

/**
 * Created with IntelliJ IDEA.
 * User: SuQiEr
 * Date:2017/6/3 0003
 * Time: 上午 11:13
 * To change this template use File | Settings | File Templates.
 */
@Slf4j
public class CrateClientDemo {

    private static Connection conn;

    @Before
    public void init(){
        try {
            conn = DriverManager.getConnection("crate://139.224.226.206:5432/");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @After
    public void close() throws SQLException {
        if(conn != null ){
            conn.close();
        }
    }

    //插入
    @Test
    public void testInsert() throws SQLException {

        //age, created_at,	id,	retweeted,	source,	text,	user_id
        StringBuffer sb = new StringBuffer("insert into tweets2 (age, created_at,id,retweeted,source,text,user_id) values ");
        int size = 500;
        int maxId = testSelectMaxId();
        maxId += 1;
        for (int i=0 ; i < size; i++){
            sb.append("(");
            sb.append(new Random().nextInt(500)).append(",");
            sb.append(System.currentTimeMillis()+new Random().nextInt(50000)).append(",");
            sb.append(maxId+i).append(",");
            sb.append(i / 2 == 0 ? true:false).append(",");
            sb.append("'jdbc'").append(",");
            sb.append("'"+UUID.randomUUID().toString()).append("',");
            sb.append("'唐坤'");
            sb.append(")");
            if(i < size -1){
                sb.append(",");
            }
        }

        log.info("sql:{}",sb.toString());

        PreparedStatement preparedStatement = conn.prepareStatement(sb.toString());
        boolean execute = preparedStatement.execute();
        log.info("insert success :{}",execute);
    }
    //插入
    @Test
    public void testInsertUser() throws SQLException {

        //age, created_at,	id,	retweeted,	source,	text,	user_id
        StringBuffer sb = new StringBuffer("insert into user (age, created_at,id,retweeted,source,text,user_id) values ");
        int size = 500;
        int maxId = testSelectMaxId();
        maxId += 1;
        for (int i=0 ; i < size; i++){
            sb.append("(");
            sb.append(new Random().nextInt(500)).append(",");
            sb.append(System.currentTimeMillis()+new Random().nextInt(50000)).append(",");
            sb.append(maxId+i).append(",");
            sb.append(i / 2 == 0 ? true:false).append(",");
            sb.append("'jdbc'").append(",");
            sb.append("'"+UUID.randomUUID().toString()).append("',");
            sb.append("'唐坤'");
            sb.append(")");
            if(i < size -1){
                sb.append(",");
            }
        }

        log.info("sql:{}",sb.toString());

        PreparedStatement preparedStatement = conn.prepareStatement(sb.toString());
        boolean execute = preparedStatement.execute();
        log.info("insert success :{}",execute);
    }
    //查询
    @Test
    public void testSelect() throws SQLException {
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery("select age from tweets2");
        while (resultSet.next()){
            Integer age = resultSet.getInt("age");
            log.info("age:{}",age);
            break;
        }
    }


    //查询最大
    public Integer testSelectMaxId() throws SQLException {
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery("select max(id) as maxId from tweets2");
        while (resultSet.next()){
            return resultSet.getInt("maxId");
        }
        return 0;
    }
}
